package com.itheima.dao.impl;


import com.itheima.dao.BaseDao;
import com.itheima.dao.ContactDao;
import com.itheima.domain.Contact;
import com.itheima.domain.ContactCondition;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ContactImpl extends BaseDao implements ContactDao {

    @Override
    public List<Contact> queryAll() throws SQLException {
        String sql = "select *from contact;";
        return queryRunner.query(sql, new BeanListHandler<>(Contact.class));
    }

    @Override
    public int add(Contact con) throws SQLException {
        String sql = "insert into contact values(null,?,?,?,?,?,?,?);";
        return queryRunner.update(sql, con.getName(), con.getSex(), con.getAge(), con.getAddress(), con.getQq(), con.getEmail(), con.getCreateDate());
    }


    @Override
    public Contact queryById(int id) throws SQLException {
        String sql = "select *from contact where id =?;";
        return queryRunner.query(sql, new BeanHandler<>(Contact.class), id);
    }

    @Override
    public int update(Contact con) throws SQLException {
        String sql = "update contact set name=?,sex=?,age=?,address=?,qq=?,email=?,createDate=? where id=?;";
        return queryRunner.update(sql, con.getName(), con.getSex(), con.getAge(), con.getAddress(), con.getQq(), con.getEmail(), con.getCreateDate(), con.getId());
    }

    @Override
    public int delete(int id) throws SQLException {
        String sql = "delete from contact where id =?;";
        return queryRunner.update(sql, id);
    }

    @Override
    public Integer queryForPageTotalCount() throws SQLException {
        String sql = "select count(*) from contact;";
        Object query = queryRunner.query(sql, new ScalarHandler());
        System.out.println(query);
        return Integer.parseInt(query.toString());
    }

    @Override
    public List<Contact> queryForPageItems(int begin, int pageSize) throws SQLException {
        String sql = "select *from contact limit ?,?;";
        return queryRunner.query(sql, new BeanListHandler<>(Contact.class), begin, pageSize);
    }

    @Override
    public List<Contact> queryForCondition(ContactCondition cc) throws SQLException {
        List<Object> list = new ArrayList<>();
        String sql = "";
        sql += "select *from contact where 1 = 1";
        if (cc.getNameLike() != null) {
            sql += " and name like ? ";
            list.add("%" + cc.getNameLike() + "%");
        }
        if (cc.getFromAge() != null) {
            sql += " and age> ?";
            list.add(cc.getFromAge());
        }
        if (cc.getToAge() != null) {
            sql += " and age< ?";
            list.add(cc.getToAge());
        }
        sql += ";";
        return queryRunner.query(sql, new BeanListHandler<>(Contact.class), list.toArray());
    }
}
